package com.yanhui.utils.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class _jdbcUtils {

	public static void main(String[] args) throws Exception {
		List<Map<String,Object>> list = _jdbcUtils.testQuery();
		int i = 0;
		for (Map<String,Object> s : list) {
//			String gldx_shop_id = _jdbcUtils.testGldxShopId(s.get("id").toString());
			System.out.println(s.get("id").toString());
			Map<String, Object> dataMap = new HashMap<String, Object>();
			dataMap.put("by1_", "''");
			dataMap.put("hasUpload_", 0);
			String idField = "id = '"+s.get("id").toString()+"'";
			_jdbcUtils.updateByTable("gldx_shop", dataMap , idField);
			i++;
		}
		System.out.println(i);
	}

	@SuppressWarnings("unused")
	private static void testOneInsert(Map<String,Object> s, String gldx_shop_id)  
	{  
		String SQL = "INSERT INTO gldx_shop_xkz"
			+" (id,gldx_shop_id,xkz_,fzjg_,bdate_,edate_,status_,jyfw_,xkz_year_)"
			+" SELECT lower(replace(NEWID(),'-','')),'"+gldx_shop_id+"',jyxkz_,fzdw_,date_,endDate_,0,jyfw_,year_"
			+" FROM gldx_shop where id = '"+s.get("id")+"';";  
		if(_jdbcUtils.executeUpdate(SQL))  
		{  
			System.out.println("插入成功 ");  
		}else  
		{  
			System.out.println("插入失败 ");  
		}  
	}  

	//	/select id as gldx_shop_id from gldx_shop where account_ = '13719201996' and by1_ is null
	@SuppressWarnings("unused")
	private static String testGldxShopId(String account) throws SQLException  
	{  
		String gldx_shop_id = "";
		String SQL = "select id as gldx_shop_id from gldx_shop where account_ = '"+account+"' and by1_ is null";  
		ResultSet rs=_jdbcUtils.executeQuery(SQL);  
		while (rs.next())   
		{  
			gldx_shop_id = rs.getString("gldx_shop_id");
		}  
		return gldx_shop_id;
	}  

	@SuppressWarnings("unused")
	private static List<Map<String,Object>> testQuery() throws SQLException  
	{  
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		String SQL = "select id,by1_,account_,jyxkz_,fzdw_,date_,endDate_,0,jyfw_,year_ from gldx_shop" 
//				"where account_ in ("
//			+"select account_ from gldx_shop  group by account_ having count(id)>1"  
//			+")" 
//			+" and by1_ is not NULL"
			+" where by1_ like '用户名已经存在%'";
//			+" and systemUserTime_ < '2017-12-25 12:57:00'"
//			+" order by systemUserTime_ desc";  
		ResultSet rs=_jdbcUtils.executeQuery(SQL);  
		while (rs.next())   
		{  
			Map<String,Object> map = new HashMap<String, Object>();
			map.put("id", rs.getString("id"));
			map.put("account_", rs.getString("account_"));
			map.put("jyxkz_", rs.getString("jyxkz_"));
			map.put("fzdw_", rs.getString("fzdw_"));
			map.put("date_", rs.getString("date_"));
			map.put("endDate_", rs.getString("endDate_"));
			map.put("jyfw_", rs.getString("jyfw_"));
			map.put("year_", rs.getString("year_"));
			list.add(map);
		}  
		return list;
	}  

	private static final String driverName="com.mysql.jdbc.Driver";//SQL数据库引擎
	private static final String dbURL="jdbc:mysql://127.0.0.1:3306/db_ticket";//数据源  ！！！！注意若出现加载或者连接数据库失败一般是这里出现问题
	private static final String userName="root";
	private static final String userPwd="934247746";
//	private static final String driverName="com.microsoft.sqlserver.jdbc.SQLServerDriver";//SQL数据库引擎
//	private static final String dbURL="jdbc:sqlserver://192.168.30.71:1433;DatabaseName=db_gdsyjg";//数据源  ！！！！注意若出现加载或者连接数据库失败一般是这里出现问题
//	private static final String userName="user_dev";
//	private static final String userPwd="user@dev";

	private static Connection  getCoonection()  
	{  
		try  
		{  
			Class.forName(driverName);  
			Connection conn=DriverManager.getConnection(dbURL,userName,userPwd);  
			return conn;  
		}  

		catch(Exception e)  
		{  
			e.printStackTrace();  
			System.out.print("----------------连接失败");  
		}   
		return null;  
	}  

	public static ResultSet  executeQuery(String SQL)  
	{    
		try  

		{  

			Connection conn=getCoonection();  
//			System.out.println("---------------连接数据库成功");    
			// String SQL = "SELECT PlanTypeID, PlanTypeName FROM C_PlanType ";  
			Statement stmt = conn.createStatement();  
			ResultSet rs = stmt.executeQuery(SQL);  
			/* while (rs.next())  
             { 
                System.out.println(rs.getString("PlanTypeID") + ", " + rs.getString("PlanTypeName")); 
             }*/  
			// rs.close();  
			// stmt.close();   
			return  rs;  
		}  
		catch(Exception e)  
		{  
			e.printStackTrace();  
			System.out.print("----------------查询失败");  
		}  
		return null;  
	}  
	public static boolean  executeUpdate(String SQL)  
	{    
		try  
		{  
			Connection conn=getCoonection();  
			System.out.println("---------------连接数据库成功");    

			Statement stmt = conn.createStatement();  
			int result = stmt.executeUpdate(SQL);  
			if(result>0)  
				return true;  
		}  
		catch(Exception e)  
		{  
			e.printStackTrace();  
			System.out.print("----------------更新失败");  
		}  
		return false;  
	}  

	@SuppressWarnings("unused")
	private static void testInsert()  
	{  
		String SQL = "  insert into C_PlanType(PlanTypeID,PlanTypeName,DisplayOrder)values('7','测试','7') ";  
		if(_jdbcUtils.executeUpdate(SQL))  
		{  
			System.out.println("插入成功 ");  
		}else  
		{  
			System.out.println("插入失败 ");  
		}  
	}  
	@SuppressWarnings("unused")
	private static void testUpdate()  
	{  
		String  SQL = "  update  C_PlanType set PlanTypeName='测试修改'  where PlanTypeID='7'";  
		if(_jdbcUtils.executeUpdate(SQL))  
		{  
			System.out.println("更新成功 ");  
		}else  
		{  
			System.out.println("更新失败 ");  
		}  
	}  
	@SuppressWarnings("unused")
	private static void testDelete()  
	{  
		String SQL = "  delete from  C_PlanType   where PlanTypeID='7'";  
		if(_jdbcUtils.executeUpdate(SQL))  
		{  
			System.out.println("删除成功 ");  
		}else  
		{  
			System.out.println("删除失败 ");  
		}  
	}  
	
	
	@SuppressWarnings("unused")
	private static List<Map<String,Object>> queryBySql(String sql, List<String> fieldList) throws SQLException  
	{  
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		String SQL = sql;  
		ResultSet rs=_jdbcUtils.executeQuery(SQL);  
		while (rs.next())   
		{  
			Map<String,Object> map = new HashMap<String, Object>();
			for(String field : fieldList){
				map.put(field, rs.getString(field));
			}
			list.add(map);
		}  
		return list;
	}  
	
	@SuppressWarnings("unused")
	private static void insertByTable(String tableName,Map<String,Object> dataMap)  
	{  
		String fieldKey = ""; 
		String fieldValue = ""; 
		for (Object key : dataMap.keySet()) {  
			Object value = dataMap.get(key);  
			String name_ = key.toString();
			String value_ = value.toString();
			fieldKey +=name_+",";
			fieldValue +=value_+",";
		}
		fieldKey = fieldKey.substring(0,
				fieldKey.length() - 1);
		fieldValue = fieldValue.substring(0, fieldValue
					.length() - 1);
		
		String SQL = " insert into "+tableName+"("+fieldKey+")values("+fieldValue+")";  
		if(_jdbcUtils.executeUpdate(SQL))  
		{  
			System.out.println("插入成功 ");  
		}else  
		{  
			System.out.println("插入失败 ");  
		}  
	}
	@SuppressWarnings("unused")
	private static void insertBySql(String sql)  
	{  
		String SQL = sql;  
		if(_jdbcUtils.executeUpdate(SQL))  
		{  
			System.out.println("插入成功 ");  
		}else  
		{  
			System.out.println("插入失败 ");  
		}  
	}
	
	@SuppressWarnings("unused")
	private static void updateBySql(String sql)  
	{  
		String  SQL = sql;  
//		String  SQL = "  update  C_PlanType set PlanTypeName='测试修改'  where PlanTypeID='7'";  
		if(_jdbcUtils.executeUpdate(SQL))  
		{  
			System.out.println("更新成功 ");  
		}else  
		{  
			System.out.println("更新失败 ");  
		}  
	} 
	
	@SuppressWarnings("unused")
	private static void updateByTable(String tableName,Map<String,Object> dataMap,String idField)  
	{  
		String fieldSet = ""; 
		for (Object key : dataMap.keySet()) {  
			Object value = dataMap.get(key);  
			String name_ = key.toString();
			String value_ = value.toString();
			fieldSet +=name_+"="+value_+",";
		}
		fieldSet = fieldSet.substring(0,
				fieldSet.length() - 1);
		
		String SQL = "update "+tableName+" set "+fieldSet+" where "+idField;  
		if(_jdbcUtils.executeUpdate(SQL))  
		{  
			System.out.println("更新成功 ");  
		}else  
		{  
			System.out.println("更新失败 ");  
		}  
	}
	
//	public static void main(String[] args) throws Exception {
//		String sql = "insert into test01(id,name) values(2,'你')";
//		String sql = "select * from gldx_shop";
//		List<String> fieldList = new ArrayList<String>();
//		fieldList.add("id");
//		fieldList.add("name_");
		/*List<Map<String,Object>> list = _jdbcUtils.queryBySql(sql, fieldList);
		for (Map<String,Object> s : list) {
			System.out.println(s.get("name"));
		}*/
//		_jdbcUtils.insertBySql(sql);
//		String tableName = "test01";
//		Map<String, Object> dataMap = new HashMap<String, Object>();
//		dataMap.put("id", 3);
//		dataMap.put("name", "'df'");
//		_jdbcUtils.insertByTable(tableName, dataMap);
//		String sql = "update test01 set name='你2' where id=2";
//		_jdbcUtils.updateBySql(sql);
//		String tableName = "test01";
//		Map<String, Object> dataMap = new HashMap<String, Object>();
//		dataMap.put("name", "'你3'");
//		String idField = "id = 2";
//		_jdbcUtils.updateByTable(tableName, dataMap, idField);
//		String sql = "delete from test01 where id=2";
//		_jdbcUtils.deleteBySql(sql);
//		String tableName = "test01";
//		String idField = "id = 4";
//		_jdbcUtils.deleteByTable(tableName, idField);
//		
//	}
	
	@SuppressWarnings("unused")
	private static void deleteBySql(String sql)  
	{  
		String SQL = sql;  
		if(_jdbcUtils.executeUpdate(SQL))  
		{  
			System.out.println("删除成功 ");  
		}else  
		{  
			System.out.println("删除失败 ");  
		}  
	}  
	@SuppressWarnings("unused")
	private static void deleteByTable(String tableName, String idField)  
	{  
		String SQL = "delete from  "+tableName+" where "+idField;  
		if(_jdbcUtils.executeUpdate(SQL))  
		{  
			System.out.println("删除成功 ");  
		}else  
		{  
			System.out.println("删除失败 ");  
		}  
	}  
}  



